#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
读取产品原物料报货和消耗数据，按门店编号和主料名称透视，然后拼接
"""

import pandas as pd
import sqlite3

# 从SQLite数据库读取报货和消耗数据
try:
    conn = sqlite3.connect('temp.db')
    df_report = pd.read_sql_query("SELECT * FROM report_query", conn)
    df_consumption = pd.read_sql_query("SELECT * FROM sales_query", conn)
    conn.close()
except Exception as e:
    print(f"从数据库读取数据时发生错误: {e}")
    exit(1)

# 按门店编号和主料名称透视报货数据，计算报货总量sum和报货信息
try:
    # 定义一个函数来拼接存货名称和数量
    def combine_inventory_info(group):
        # 将数量转换为整数
        group['数量'] = group['数量'].astype(int)
        combined = '；'.join([f"{row['存货名称']}，{row['数量']}" for _, row in group.iterrows()])
        return pd.Series({'报货总量_sum': group['报货总量'].sum(), '报货': combined})
    df_report_pivot = df_report.groupby(['门店编号', '主料名称']).apply(combine_inventory_info, include_groups=False).reset_index()
except KeyError as e:
    print(f"报货数据缺少必要列: {e}")
    exit(1)

# 按门店编号和主料名称透视消耗数据，计算主料消耗量sum和销售信息
try:
    # 定义一个函数来拼接产品名称和销量
    def combine_sales_info(group):
        # 将主料消耗量转换为整数
        group['主料消耗量'] = group['主料消耗量'].astype(int)
        combined = '；'.join([f"{row['产品名称']}，{row['销量']}，{row['主料消耗量']}" for _, row in group.iterrows()])
        return pd.Series({'主料消耗量_sum': group['主料消耗量'].sum(), '销售': combined})
    df_consumption_pivot = df_consumption.groupby(['门店编号', '主料名称']).apply(combine_sales_info, include_groups=False).reset_index()

except KeyError as e:
    print(f"消耗数据缺少必要列: {e}")
    exit(1)

# 按门店编号和主料名称拼接两个数据集
try:
    df_merged = pd.merge(df_report_pivot, df_consumption_pivot, on=['门店编号', '主料名称'], how='outer')
except Exception as e:
    print(f"拼接数据时发生错误: {e}")
    exit(1)

# 计算报货比
try:
    df_merged['报货比'] = df_merged['主料消耗量_sum'] / df_merged['报货总量_sum']
except Exception as e:
    print(f"计算报货比时发生错误: {e}")
    exit(1)

# 重新排列列的顺序并重命名列
try:
    # 重新排列列的顺序
    df_merged = df_merged.loc[:, ['门店编号', '主料名称', '报货总量_sum', '主料消耗量_sum', '报货比', '报货', '销售']]
    # 重命名列
    df_merged.rename(columns={'报货总量_sum': '报货总量', '主料消耗量_sum': '主料消耗量'}, inplace=True)
except Exception as e:
    print(f"重新排列列顺序或重命名列时发生错误: {e}")
    exit(1)

# 保存结果到新的Excel文件
try:
    df_merged.to_excel('报货消耗对比.xlsx', index=False)
    print("报货消耗对比数据已保存至报货消耗对比.xlsx")
except Exception as e:
    print(f"保存文件时发生错误: {e}")
    exit(1)